package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import com.cn.wanxi.util.MyTool;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserImpl implements UserDao {
    /**
     * 1.封装sql语句
     * 2.执行sql语句  调用JDBC
     * 3.封装返回结果
     *
     * @param userModel
     * @return
     */
    @Override
    public int add(UserModel userModel) {
        String sql = "insert into user (username,password," +
                "sex,phone," +
                "hobby,birthday," +
                "create_time," +
                "update_time,enable) " +
                "values('" + userModel.getUsername() + "','" + userModel.getPassword() + "'," +
                "'" + userModel.getSex() + "','" + userModel.getPhone() + "'," +
                "'" + userModel.getHobby() + "','" + userModel.getBirthday() + "'," +
                "'" + MyTool.getDate() + "'," +
                "now(),0)";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username='" + username + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
//                userModel.setPhone(resultSet.getString("phone"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public List<UserModel> findAll() {
        String sql = "select * from user order by id desc";//倒序排序
        return setList(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user where  true ";
        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += "and phone like '%" + userModel.getPhone() + "%' ";
        }
        if (!"".equals(userModel.getEmail())) {
            sql += "and email like '%" + userModel.getPhone() + "%' ";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += "and birthday <='" + userModel.getEndBirthday() + "' ";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += "and birthday >='" + userModel.getStartBirthday() + "' ";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += "and birthday  between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "' ";
        }
        if (!"全部".equals(userModel.getSex())) {
            sql += "and sex ='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += "and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "   find_in_set('" + hobbys[i] + "',hobby)";
                } else {
                    sql += "  or find_in_set('" + hobbys[i] + "',hobby)";
                }
            }
            sql += ")";
        }
        sql += " limit " + (userModel.getPageModel().getPageNo() + -1) * +userModel.getPageModel().getPageSize() + ", " + userModel.getPageModel().getPageSize() + "";

        return setList(sql);
    }

    /**
     * 查询多少条数据
     *
     * @return
     */
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where  true ";
        if (!"".equals(userModel.getUsername())) {
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += "and phone like '%" + userModel.getPhone() + "%' ";
        }
        if (!"".equals(userModel.getEmail())) {
            sql += "and email like '%" + userModel.getPhone() + "%' ";
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += "and birthday <='" + userModel.getEndBirthday() + "' ";
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql += "and birthday >='" + userModel.getStartBirthday() + "' ";
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql += "and birthday  between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "' ";
        }
        if (!"全部".equals(userModel.getSex())) {
            sql += "and sex ='" + userModel.getSex() + "'";
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql += "and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "   find_in_set('" + hobbys[i] + "',hobby)";
                } else {
                    sql += "  or find_in_set('" + hobbys[i] + "',hobby)";
                }
            }
            sql += ")";
        }

        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private List<UserModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel userModel = new UserModel();
                userModel.setId(resultSet.getInt("id"));
                userModel.setUsername(resultSet.getString("username"));
                userModel.setPhone(resultSet.getString("phone"));
                userModel.setEmail(resultSet.getString("email"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setBirthday(resultSet.getString("birthday"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setHeight(resultSet.getInt("height"));
                userModel.setCreateTime(resultSet.getString("create_time"));
                userModel.setEnable(resultSet.getInt("enable"));
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
//                userModel.setPhone(resultSet.getString("phone"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int del(Integer id) {
        String sql = "delete from user where id=" + id;
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(int id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setPhone(resultSet.getString("phone"));
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEnable(resultSet.getInt("enable"));
                model.setEmail(resultSet.getString("email"));
                model.setHeight(resultSet.getInt("height"));


            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }


}
